import openpyxl
import pymysql
from DButils import DButils
#1.创建表和sheet页
# workbook = openpyxl.Workbook('user.xlsx')
# workbook.create_sheet(index=0,title='user_sheet')
# workbook.save('user.xlsx')



#2.写入数据
# workbook = openpyxl.load_workbook('user.xlsx')
# worksheet = workbook['user_sheet']
# for row in range(1, 2001):
#         for col in range(1, 11):
#             # 生成 1
#             worksheet.cell(row=row, column=col, value=1)
# workbook.save('user.xlsx')


#3.读取出来
class ReadExcel:
    @staticmethod
    def read_excel(file_name,sheet_name):
        workbook = openpyxl.load_workbook(file_name)
        sheet = workbook[sheet_name]
        data = [[sheet.cell(row=i,column=j).value for j in range(1,sheet.max_column+1)] for i in range(1,sheet.max_row+1)]
        return data


if __name__ == '__main__':
    db = DButils('127.0.0.1', 'root', '123456', 'pythondbutils')
    sql = "insert into user_test values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    data = ReadExcel.read_excel('user.xlsx','user_sheet')
    db.many_upgrade(sql,data)
